Skip Headers

Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2) Part Number B14239-04 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# A Troubleshooting Data Guard

This appendix provides help troubleshooting a standby database. This appendix contains the following sections:

- Common Problems

- Log File Destination Failures

- Handling Logical Standby Database Failures

- Problems Switching Over to a Standby Database

- What to Do If SQL Apply Stops

- Network Tuning for Redo Data Transmission

- Slow Disk Performance on Standby Databases

- Log Files Must Match to Avoid Primary Database Shutdown

- Troubleshooting a Logical Standby Database

## A.1 Common Problems

If you encounter a problem when using a standby database, it is probably because of one of the following reasons:

- Standby Archive Destination Is Not Defined Properly

- Renaming Datafiles with the ALTER DATABASE Statement

- Standby Database Does Not Receive Redo Data from the Primary Database

- You Cannot Mount the Physical Standby Database

### A.1.1 Standby Archive Destination Is Not Defined Properly

If the `STANDBY_ARCHIVE_DEST` initialization parameter does not specify a valid directory name on the standby database, the Oracle database will not be able to determine the directory in which to store the archived redo log files. Check the `DESTINATION` and `ERROR` columns in the `V$ARCHIVE_DEST` view by entering the following query and ensure the destination is valid:

    SQL> SELECT DESTINATION, ERROR FROM V$ARCHIVE_DEST;

### A.1.2 Renaming Datafiles with the ALTER DATABASE Statement

You cannot rename the datafile on the standby site when the `STANDBY_FILE_MANAGEMENT` initialization parameter is set to `AUTO`. When you set the `STANDBY_FILE_MANAGEMENT` initialization parameter to `AUTO`, use of the following SQL statements is not allowed:

- `ALTER DATABASE RENAME`

- `ALTER DATABASE ADD/DROP LOGFILE`

- `ALTER DATABASE ADD/DROP STANDBY LOGFILE MEMBER`

- `ALTER DATABASE CREATE DATAFILE AS`

If you attempt to use any of these statements on the standby database, an error is returned. For example:

    SQL> ALTER DATABASE RENAME FILE '/disk1/oracle/oradata/payroll/t_db2.log' to 'dummy';
    alter database rename file '/disk1/oracle/oradata/payroll/t_db2.log' to 'dummy'
    *
    ERROR at line 1:
    ORA-01511: error in renaming log/datafiles
    ORA-01270: RENAME operation is not allowed if STANDBY_FILE_MANAGEMENT is auto

See Section 8.3.1 to learn how to add datafiles to a physical standby database.

### A.1.3 Standby Database Does Not Receive Redo Data from the Primary Database

If the standby site is not receiving redo data, query the `V$ARCHIVE_DEST` view and check for error messages. For example, enter the following query:

    SQL> SELECT DEST_ID "ID",
      2> STATUS "DB_status",
      3> DESTINATION "Archive_dest",
      4> ERROR "Error"
      5> FROM V$ARCHIVE_DEST WHERE DEST_ID <=5;

    ID DB_status Archive_dest                   Error
    -- --------- ------------------------------ ------------------------------------
     1  VALID    /vobs/oracle/work/arc_dest/arc
     2  ERROR    standby1                       ORA-16012: Archivelog standby database identifier mismatch
     3  INACTIVE
     4  INACTIVE
     5  INACTIVE
    5 rows selected.

If the output of the query does not help you, check the following list of possible issues. If any of the following conditions exist, redo transport services will fail to transmit redo data to the standby database:

- The service name for the standby instance is not configured correctly in the `tnsnames.ora` file for the primary database.

- The Oracle Net service name specified by the `LOG_ARCHIVE_DEST_``n` parameter for the primary database is incorrect.

- The `LOG_ARCHIVE_DEST_STATE_``n` parameter for the standby database is not set to the value `ENABLE.`

- The `listener.ora` file has not been configured correctly for the standby database.

- The listener is not started at the standby site.

- The standby instance is not started.

- You have added a standby archiving destination to the primary SPFILE or text initialization parameter file, but have not yet enabled the change.

- The databases in the Data Guard configuration are not all using a password file, or the SYS password contained in the password file is not identical on all systems.

- You used an invalid backup as the basis for the standby database (for example, you used a backup from the wrong database, or did not create the standby control file using the correct method).

### A.1.4 You Cannot Mount the Physical Standby Database

You cannot mount the standby database if the standby control file was not created with the `ALTER DATABASE CREATE [LOGICAL] STANDBY CONTROLFILE ...` statement or RMAN command. You cannot use the following types of control file backups:

- An operating system-created backup

- A backup created using an `ALTER DATABASE` statement without the `PHYSICAL STANDBY` or `LOGICAL STANDBY` option

## A.2 Log File Destination Failures

If you specify `REOPEN` for an `OPTIONAL` destination, it is possible for the Oracle database to reuse online redo log files even if there is an error archiving to the destination in question. If you specify `REOPEN` for a `MANDATORY` destination, redo transport services stall the primary database when redo data cannot be successfully transmitted.

The `REOPEN` attribute is required when you use the `MAX_FAILURE` attribute. Example A-1 shows how to set a retry time of 5 seconds and limit retries to 3 times.

Example A-1 Setting a Retry Time and Limit

    LOG_ARCHIVE_DEST_1='LOCATION=/arc_dest REOPEN=5 MAX_FAILURE=3'

Use the `ALTERNATE` attribute of the `LOG_ARCHIVE_DEST_``n` parameter to specify alternate archive destinations. An alternate archiving destination can be used when the transmission of redo data to a standby database fails. If transmission fails and the `REOPEN` attribute was not specified or the `MAX_FAILURE` attribute threshold was exceeded, redo transport services attempts to transmit redo data to the alternate destination on the next archival operation.

Use the `NOALTERNATE` attribute to prevent the original archive destination from automatically changing to an alternate archive destination when the original archive destination fails.

Example A-2 shows how to set the initialization parameters so that a single, mandatory, local destination will automatically fail over to a different destination if any error occurs.

Example A-2 Specifying an Alternate Destination

    LOG_ARCHIVE_DEST_1='LOCATION=/disk1 MANDATORY ALTERNATE=LOG_ARCHIVE_DEST_2'
    LOG_ARCHIVE_DEST_STATE_1=ENABLE
    LOG_ARCHIVE_DEST_2='LOCATION=/disk2 MANDATORY'
    LOG_ARCHIVE_DEST_STATE_2=ALTERNATE

If the `LOG_ARCHIVE_DEST_1` destination fails, the archiving process will automatically switch to the `LOG_ARCHIVE_DEST_2` destination at the next log file switch on the primary database.

## A.3 Handling Logical Standby Database Failures

An important tool for handling logical standby database failures is the `DBMS_LOGSTDBY.SKIP_ERROR` procedure. Depending on how important a table is, you might want to do one of the following:

- Ignore failures for a table or specific DDL

- Associate a stored procedure with a filter so at runtime a determination can be made about skipping the statement, executing this statement, or executing a replacement statement

Taking one of these actions prevents SQL Apply from stopping. Later, you can query the `DBA_LOGSTDBY_EVENTS` view to find and correct any problems that exist. See Oracle Database PL/SQL Packages and Types Reference for more information about using the `DBMS_LOGSTDBY` package with PL/SQL callout procedures.

## A.4 Problems Switching Over to a Standby Database

In most cases, following the steps described in Chapter 7 will result in a successful switchover. However, if the switchover is unsuccessful, the following sections may help you to resolve the problem:

- Switchover Fails Because Redo Data Was Not Transmitted

- Switchover Fails Because SQL Sessions Are Still Active

- Switchover Fails Because User Sessions Are Still Active

- Switchover Fails with the ORA-01102 Error

- Redo Data Is Not Applied After Switchover

- Roll Back After Unsuccessful Switchover and Start Over

### A.4.1 Switchover Fails Because Redo Data Was Not Transmitted

If the switchover does not complete successfully, you can query the `SEQUENCE#` column in the `V$ARCHIVED_LOG` view to see if the last redo data transmitted from the original primary database was applied on the standby database. If the last redo data was not transmitted to the standby database, you can manually copy the archived redo log file containing the redo data from the original primary database to the old standby database and register it with the S QL `ALTER DATABASE REGISTER LOGFILE` file_specification statement. If you then start log apply services, the archived redo log file will be applied automatically. Query the `SWITCHOVER_STATUS` column in the `V$DATABASE` view. The `TO PRIMARY` value in the `SWITCHOVER_STATUS` column verifies switchover to the primary role is now possible.

    SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
    SWITCHOVER_STATUS
    -----------------
    TO PRIMARY
    1 row selected

See Chapter 16 for information about other valid values for the `SWITCHO` `VER_STATUS` column of the `V$DATABASE` view.

To continue with the switchover, follow the instructions in Section 7.2.1 for physical standby databases or Section 7.3.1 for logical standby databases, and try again to switch the target standby database to the primary role.

### A.4.2 Switchover Fails Because SQL Sessions Are Still Active

If you do not include the `WITH SESSION SHUTDOWN` clause as a part of the `ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY` statement, active SQL sessions might prevent a switchover from being processed. Active SQL sessions can include other Oracle Database processes.

When sessions are active, an attempt to switch over fails with the following error message:

    SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
    ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY *
    ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

Action: Query the `V$SESSION` view to determine which processes are causing the error. For example:

    SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION
      2> WHERE TYPE = 'USER'
      3>  AND SID <> (SELECT DISTINCT SID FROM V$MYSTAT);
    SID        PROCESS   PROGRAM
    ---------  --------  ------------------------------------------------
            7      3537  oracle@nhclone2 (CJQ0)
           10
           14
           16
           19
           21
     6 rows selected.

In the previous example, the `JOB_QUEUE_PROCESSES` parameter corresponds to the CJQ0 process entry. Because the job queue process is a user process, it is counted as a SQL session that prevents switchover from taking place. The entries with no process or program information are threads started by the job queue controller.

Verify the `JOB_QUEUE_PROCESSES` parameter is set using the following SQL statement:

    SQL> SHOW PARAMETER JOB_QUEUE_PROCESSES;
    NAME                           TYPE      VALUE
    ------------------------------ -------   --------------------
    job_queue_processes            integer   5

Then, set the parameter to 0. For example:

    SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
    Statement processed.

Because `JOB_QUEUE_PROCESSES` is a dynamic parameter, you can change the value and have the change take effect immediately without having to restart the instance. You can now retry the switchover procedure.

Do not modify the parameter in your initialization parameter file. After you shut down the instance and restart it after the switchover completes, the parameter will be reset to the original value. This applies to both primary and physical standby databases.

Table A-1 summarizes the common processes that prevent switchover and what corrective action you need to take.

Table A-1 Common Processes That Prevent Switchover

Type of Process | Process Description | Corrective Action
--- | --- | ---
CJQ0 | Job Queue Scheduler Process | Change the `JOB_QUEUE_PROCESSES` dynamic parameter to the value 0. The change will take effect immediately without having to restart the instance.
QMN0 | Advanced Queue Time Manager | Change the `AQ_TM_PROCESSES` dynamic parameter to the value 0. The change will take effect immediately without having to restart the instance.
DBSNMP | Oracle Enterprise Manager Management Agent | Issue the `emctl stop agent` command from the operating system prompt.

### A.4.3 Switchover Fails Because User Sessions Are Still Active

If the switchover fails and returns the error ORA-01093 "Alter database close only permitted with no sessions connected" it is usually because the `ALTER DATABASE COMMIT TO SWITCHOVER` statement implicitly closed the database, and if there are any other user sessions connected to the database, the close fails.

If you receive this error, disconnect any user sessions that are still connected to the database. To do this, query the `V$SESSION` fixed view to see which sessions are still active as shown in the following example:

    SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION;

           SID PROCESS   PROGRAM
    ---------- --------- ------------------------------------------------
             1 26900     oracle@dbuser-sun (PMON)
             2 26902     oracle@dbuser-sun (DBW0)
             3 26904     oracle@dbuser-sun (LGWR)
             4 26906     oracle@dbuser-sun (CKPT)
             5 26908     oracle@dbuser-sun (SMON)
             6 26910     oracle@dbuser-sun (RECO)
             7 26912     oracle@dbuser-sun (ARC0)
             8 26897     sqlplus@dbuser-sun (TNS V1-V3)
            11 26917     sqlplus@dbuser-sun (TNS V1-V3)

    9 rows selected.

In this example, the first seven sessions are all Oracle Database background processes. Among the two SQL*Plus sessions, one is the current SQL*Plus session issuing the query, and the other is an extra session that should be disconnected before you re-attempt the switchover.

### A.4.4 Switchover Fails with the ORA-01102 Error

Suppose the standby database and the primary database reside on the same site. After both the `ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL` `STANDBY` and the `ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY` statements are successfully executed, shut down and restart the physical standby database and the primary database.

Note: It is not necessary to shut down and restart the physical standby database if it has not been opened read-only since the instance was started.

However, the startup of the second database fails with ORA-01102 error "cannot mount database in `EXCLUSIVE` mode."

This could happen during the switchover if you did not set the `DB_UNIQUE_NAME` parameter in the initialization parameter file that is used by the standby database (that is, the original primary database). If the `DB_UNIQUE_NAME` parameter of the standby database is not set, the standby and the primary databases both use the same mount lock and cause the ORA-01102 error during the startup of the second database.

Action: Add `DB_UNIQUE_NAME=``unique_database_name` to the initialization parameter file used by the standby database, and shut down and restart the standby and primary databases.

### A.4.5 Redo Data Is Not Applied After Switchover

The archived redo log files are not applied to the new standby database after the switchover.

This might happen because some environment or initialization parameters were not properly set after the switchover.

Action:

- Check the `tnsnames.ora` file at the new primary site and the `listener.ora` file at the new standby site. There should be entries for a listener at the standby site and a corresponding service name at the primary site.

- Start the listener at the standby site if it has not been started.

- Check if the `LOG_ARCHIVE_DEST_``n` initialization parameter was set to properly transmit redo data from the primary site to the standby site. For example, query the `V$ARCHIVE_DEST` fixed view at the primary site as follows:

      SQL> SELECT DEST_ID, STATUS, DESTINATION FROM V$ARCHIVE_DEST;

  If you do not see an entry corresponding to the standby site, you need to set `LOG_ARCHIVE_DEST_``n` and `LOG_ARCHIVE_DEST_STATE_``n` initialization parameters.

- Set the `STANDBY_ARCHIVE_DEST` and `LOG_ARCHIVE_FORMAT` initialization parameters correctly at the standby site so that the archived redo log files are applied to the desired location.

- At the standby site, set the `DB_FILE_NAME_CONVERT` and `LOG_FILE_NAME_CONVERT` initialization parameters. Set the `STANDBY_FILE_MANAGEMENT` initialization parameter to `AUTO` if you want the standby site to automatically add new datafiles that are created at the primary site.

### A.4.6 Roll Back After Unsuccessful Switchover and Start Over

For physical standby databases in situations where an error occurred and it is not possible to continue with the switchover, it might still be possible to revert the new physical standby database back to the primary role by using the following steps:

1. Connect to the new standby database (old primary), and issue the following statement to convert it back to the primary role:

       SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

   If this statement is successful, then shut down (if necessary) and restart the database. Once restarted, the database will be running in the primary database role, and you do not need to perform any more steps.

   If this statement is unsuccessful, then continue with Step 3.

2. When the switchover to change the role from primary to physical standby was initiated, a trace file was written in the log directory. This trace file contains the SQL statements required to re-create the original primary control file. Locate the trace file and extract the SQL statements into a temporary file. Execute the temporary file from SQL*Plus. This will revert the new standby database back to the primary role.

3. Shut down the original physical standby database.

4. Create a new standby control file. This is necessary to resynchronize the primary database and physical standby database. Copy the physical standby control file to the original physical standby system. Section 3.2.2 describes how to create a physical standby control file.

5. Restart the original physical standby instance.

   If this procedure is successful and archive gap management is enabled, the FAL processes will start and re-archive any missing archived redo log files to the physical standby database. Force a log switch on the primary database and examine the alert logs on both the primary database and physical standby database to ensure the archived redo log file sequence numbers are correct.

   See Section 5.8 for information about archive gap management and Appendix G for information about locating the trace files.

6. Try the switchover again.

   At this point, the Data Guard configuration has been rolled back to its initial state, and you can try the switchover operation again (after correcting any problems that might have led to the initial unsuccessful switchover).

## A.5 What to Do If SQL Apply Stops

Log apply services cannot apply unsupported DML statements, DDL statements, and Oracle supplied packages to a logical standby database running SQL Apply.

When an unsupported statement or package is encountered, SQL Apply stops. You can take the actions described in Table A-2 to correct the situation and start SQL Apply on the logical standby database again.

Table A-2 Fixing Typical SQL Apply Errors

If... | Then...
--- | ---
You suspect an unsupported statement or Oracle supplied package was encountered | Find the last statement in the `DBA_LOGSTDBY_EVENTS` view. This will indicate the statement and error that caused SQL Apply to fail. If an incorrect SQL statement caused SQL Apply to fail, transaction information, as well as the statement and error information, can be viewed. The transaction information can be used with LogMiner tools to understand the cause of the problem.
An error requiring database management occurred, such as running out of space in a particular tablespace | Fix the problem and resume SQL Apply using the `ALTER DATABASE START LOGICAL STANDBY APPLY` statement.
An error occurred because a SQL statement was entered incorrectly, such as an incorrect standby database filename being entered in a tablespace statement | Enter the correct SQL statement and use the `DBMS_LOGSTDBY.SKIP_TRANSACTION` procedure to ensure the incorrect statement is ignored the next time SQL Apply is run. Then, restart SQL Apply using the `ALTER DATABASE START LOGICAL STANDBY APPLY` statement.
An error occurred because skip parameters were incorrectly set up, such as specifying that all DML for a given table be skipped but `CREATE`, `ALTER`, and `DROP TABLE` statements were not specified to be skipped | Issue the `DBMS_LOGSTDBY.SKIP('TABLE','schema_name','table_name',null)` procedure, then restart SQL Apply.

See Chapter 16 for information about querying the `DBA_LOGSTDBY_EVENTS` view to determine the cause of failures.

## A.6 Network Tuning for Redo Data Transmission

For optimal performance, set the Oracle Net `SDU` parameter to 32 kilobytes in each Oracle Net connect descriptor used by redo transport services.

The following example shows a database initialization parameter file segment that defines a remote destination `netserv`:

    LOG_ARCHIVE_DEST_3='SERVICE=netserv'

The f ollowing example shows the definition of that service name in the `tnsnames.ora` file:

    netserv=(DESCRIPTION=(SDU=32768)(ADDRESS=(PROTOCOL=tcp)(HOST=host) (PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=srvc)))

The following example shows the definition in the `listener.ora` file:

    LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
    (HOST=host)(PORT=1521))))

    SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SDU=32768)(SID_NAME=sid)
    (GLOBALDBNAME=srvc)(ORACLE_HOME=/oracle)))

If you archive to a remote site using a high-latency or high-bandwidth network link, you can improve performance by using the `SQLNET.SEND_BUF_SIZE` and `SQLNET.RECV_BUF_SIZE` Oracle Net profile parameters to increase the size of the network send and receive I/O buffers.

See Oracle Database Net Services Administrator's Guide.

## A.7 Slow Disk Performance on Standby Databases

If asynchronous I/O on the file system itself is showing performance problems, try mounting the file system using the Direct I/O option or setting the `FILESYSTEMIO_OPTIONS=SETALL` initialization parameter. The maximum I/O size setting is 1 MB.

## A.8 Log Files Must Match to Avoid Primary Database Shutdown

If you have configured a standby redo log on one or more standby databases in the configuration, ensure the size of the standby redo log files on each standby database exactly matches the size of the online redo log files on the primary database.

At log switch time, if there are no available standby redo log files that match the size of the new current online redo log file on the primary database:

- The primary database will shut down if it is operating in maximum protection mode,

  or

- The RFS process on the standby database will create an archived redo log file on the standby database and write the following message in the alert log:

      No standby log files of size <#> blocks available.

For example, if the primary database uses two online redo log groups whose log files are 100K, then the standby database should have 3 standby redo log groups with log file sizes of 100K.

Also, whenever you add a redo log group to the primary database, you must add a corresponding standby redo log group to the standby database. This reduces the probability that the primary database will be adversely affected because a standby redo log file of the required size is not available at log switch time.

See Section 3.1.3, "Configure a Standby Redo Log" and Section 5.7, "Managing Log Files" for more information.

## A.9 Troubleshooting a Logical Standby Database

This section contains the following topics:

- Recovering from Errors

- Troubleshooting SQL*Loader Sessions

- Troubleshooting Long-Running Transactions

- Troubleshooting ORA-1403 Errors with Flashback Transactions

### A.9.1 Recovering from Errors

Logical standby databases maintain user tables, sequences, and jobs. To maintain other objects, you must reissue the DDL statements seen in the redo data stream.

If SQL Apply fails, an error is recorded in the `DBA_LOGSTDBY_EVENTS` table. The following sections demonstrate how to recover from two such errors.

#### A.9.1.1 DDL Transactions Containing File Specifications

DDL statements are executed the same way on the primary database and the logical standby database. If the underlying file structure is the same on both databases, the DDL will execute on the standby database as expected.

If an error was caused by a DDL transaction containing a file specification that did not match in the logical standby database environment, perform the following steps to fix the problem:

1. Use the `ALTER SESSION DISABLE` `GUARD` statement to bypass the database guard so you can make modifications to the logical standby database:

       SQL> ALTER SESSION DISABLE GUARD;

2. Execute the DDL statement, using the correct file specification, and then reenable the database guard. For example:

       SQL> ALTER TABLESPACE t_table ADD DATAFILE '/dbs/t_db.f' SIZE 100M REUSE;
       SQL> ALTER SESSION ENABLE GUARD;

3. Start SQL Apply on the logical standby database and skip the failed transaction.

       SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
         2> SKIP FAILED TRANSACTION;

In some situations, the problem that caused the transaction to fail can be corrected and SQL Apply restarted without skipping the transaction. An example of this might be when available space is exhausted. (Do not let the primary and logical standby databases diverge when skipping DDL transactions. If possible, you should manually execute a compensating transaction in place of the skipped transaction.)

The following example shows SQL Apply stopping, the error being corrected, and then restarting SQL Apply:

    SQL> SET LONG 1000
    SQL> ALTER SESSION SET NLS_DATE_FORMAT  = 'DD-MON-YY HH24:MI:SS';

    Session altered.

    SQL> SELECT EVENT_TIME, COMMIT_SCN, EVENT, STATUS FROM DBA_LOGSTDBY_EVENTS;

    EVENT_TIME              COMMIT_SCN
    ------------------ ---------------
    EVENT
    -------------------------------------------------------------------------------
    STATUS
    -------------------------------------------------------------------------------
    22-OCT-03 15:47:58

    ORA-16111: log mining and apply setting up

    22-OCT-03 15:48:04          209627
    insert into "SCOTT"."EMP"
    values
       "EMPNO" = 7900,
       "ENAME" = 'ADAMS',
       "JOB" = 'CLERK',
       "MGR" IS NULL,
       "HIREDATE" = TO_DATE('22-OCT-03', 'DD-MON-RR'),
       "SAL" = 950,
       "COMM" IS NULL,
       "DEPTNO" IS NULL
    ORA-01653: unable to extend table SCOTT.EMP by %200 bytes in tablespace T_TABLE

In the example, the `ORA-01653` message indicates that the tablespace was full and unable to extend itself. To correct the problem, add a new datafile to the tablespace. For example:

    SQL> ALTER TABLESPACE t_table ADD DATAFILE '/dbs/t_db.f' SIZE 60M;
    Tablespace altered.

Then, restart SQL Apply:

    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
    Database altered.

When SQL Apply restarts, the transaction that failed will be reexecuted and applied to the logical standby database.

#### A.9.1.2 Recovering from DML Failures

Do not use the `SKIP_TRANSACTION` procedure to filter DML failures. Not only is the DML that is seen in the events table skipped, but so is all the DML associated with the transaction. This will cause multiple tables.

DML failures usually indicate a problem with a specific table. For example, assume the failure is an out-of-storage error that you cannot resolve immediately. The following steps demonstrate one way to respond to this problem.

1. Bypass the table, but not the transaction, by adding the table to the skip list:

       SQL> EXECUTE DBMS_LOGSTDBY.SKIP('DML','SCOTT','EMP');
       SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

   From this point on, DML activity for the `SCOTT.EMP` table is not applied. After you correct the storage problem, you can fix the table, provided you set up a database link to the primary database that has administrator privileges to run procedures in the `DBMS_LOGSTDBY` package.

2. Using the database link to the primary database, drop the local `SCOTT.EMP` table and then re-create it, and pull the data over to the standby database.

       SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
       SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE('SCOTT','EMP','PRIMARYDB');
       SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

3. To ensure a consistent view across the newly instantiated table and the rest of the database, wait for SQL Apply to catch up with the primary database before querying this table. Refer to Section 9.4.6, "Adding or Re-Creating Tables On a Logical Standby Database" for a detailed example.

### A.9.2 Troubleshooting SQL*Loader Sessions

Oracle SQL*Loader provides a method of loading data from different sources into the Oracle Database. This section analyzes some of the features of the SQL*Loader utility as it pertains to SQL Apply.

Regardless of the method of data load chosen, the SQL*Loader control files contain an instruction on what to do to the current contents of the Oracle table into which the new data is to be loaded, via the keywords of `APPEND` and `REPLACE`. The following examples show how to use these keywords on a table named `LOAD_STOK`:

- When using the `APPEND keyword`, the new data to be loaded is appended to the contents of the `LOAD_STOK` table:

      LOAD DATA
      INTO TABLE LOAD_STOK APPEND

- When using the `REPLACE` keyword, the contents of the `LOAD_STOK` table are deleted prior to loading new data. Oracle SQL*Loader uses the `DELETE` statement to purge the contents of the table, in a single transaction:

      LOAD DATA
      INTO TABLE LOAD_STOK REPLACE

Rather than using the `REPLACE` keyword in the SQL*Loader script, Oracle recommends that prior to loading the data, issue the SQL*Plus `TRUNCATE TABLE` command against the table on the primary database. This will have the same effect of purging both the primary and standby databases copy of the table in a manner that is both fast and efficient because the `TRUNCATE TABLE` command is recorded in the online redo log files and is issued by SQL Apply on the logical standby database.

The SQL*Loader script may continue to contain the `REPLACE` keyword, but it will now attempt to `DELETE` zero rows from the object on the primary database. Because no rows were deleted from the primary database, there will be no redo recorded in the redo log files. Therefore, no `DELETE` statement will be issued against the logical standby database.

Issuing the `REPLACE` keyword without the DDL command `TRUNCATE TABLE` provides the following potential problems for SQL Apply when the transaction needs to be applied to the logical standby database.

- If the table currently contains a significant number of rows, then these rows need to be deleted from the standby database. Because SQL Apply is not able to determine the original syntax of the statement, SQL Apply must issue a `DELETE` statement for each row purged from the primary database.

  For example, if the table on the primary database originally had 10,000 rows, then Oracle SQL*Loader will issue a single `DELETE` statement to purge the 10,000 rows. On the standby database, SQL Apply does not know that all rows are to be purged, and instead must issue 10,000 individual `DELETE` statements, with each statement purging a single row.

- If the table on the standby database does not contain an index that can be used by SQL Apply, then the `DELETE` statement will issue a Full Table Scan to purge the information.

  Continuing with the previous example, because SQL Apply has issued 10,000 individual `DELETE` statements, this could result in 10,000 Full Table Scans being issued against the standby database.

### A.9.3 Troubleshooting Long-Running Transactions

One of the primary causes for long-running transactions in a SQL Apply environment is because of Full Table Scans. Additionally, long-running transactions could be the result of DDL operations being replicated to the standby database, such as when creating or rebuilding an index.

Identifying Long-Running Transactions

If SQL Apply is executing a single SQL statement for a long period of time, then a warning message similar to the following is reported in the alert log of the SQL Apply instance:

    Mon Feb 17 14:40:15 2003WARNING: the following transaction makes no progressWARNING: in the last 30 seconds for the given message!WARNING: xid =0x0016.007.000017b6 cscn = 1550349, message# = 28, slavid = 1knacrb: no offending session found (not ITL pressure)

Note the following about the warning message:

- This warning is similar to the warning message returned for interested transaction list (ITL) pressure, with the exception being the last line that begins with `knacrb`. The final line indicates:

  - A Full Table Scan may be occurring

  - This issue has nothing to do with interested transaction list (ITL) pressure

- This warning message is reported only if a single statement takes more than 30 seconds to execute.

It may not be possible to determine the SQL statement being executed by the long-running statement, but the following SQL statement may help in identifying the database objects on which SQL Apply is operating:

    SQL> SELECT SAS.SERVER_ID
      2       , SS.OWNER
      3       , SS.OBJECT_NAME
      4       , SS.STATISTIC_NAME
      5       , SS.VALUE
      6    FROM V$SEGMENT_STATISTICS SS
      7       , V$LOCK L
      8       , V$STREAMS_APPLY_SERVER SAS
      9   WHERE SAS.SERVER_ID = &SLAVE_ID
     10     AND L.SID = SAS.SID
     11     AND L.TYPE = 'TM'
     12     AND SS.OBJ# = L.ID1;

Additionally, you can issue the following SQL statement to identify the SQL statement that has resulted in a large number of disk reads being issued per execution:

    SQL> SELECT SUBSTR(SQL_TEXT,1,40)
      2       , DISK_READS
      3       , EXECUTIONS
      4       , DISK_READS/EXECUTIONS
      5       , HASH_VALUE
      6       , ADDRESS
      7    FROM V$SQLAREA
      8   WHERE DISK_READS/GREATEST(EXECUTIONS,1) > 1
      9     AND ROWNUM < 10
     10   ORDER BY DISK_READS/GREATEST(EXECUTIONS,1) DESC

Oracle recommends that all tables have primary key constraints defined, which automatically means that the column is defined as `NOT NULL`. For any table where a primary-key constraint cannot be defined, an index should be defined on an appropriate column that is defined as `NOT NULL`. If a suitable column does not exist on the table, then the table should be reviewed and, if possible, skipped by SQL Apply. The following steps describe how to skip all DML statements issued against the `FTS` table on the `SCOTT` schema:

1. Stop SQL Apply:

       SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
       Database altered

2. Configure the skip procedure for the `SCOTT.FTS` table for all DML transactions:

       SQL> EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'DML' , -
            schema_name => 'SCOTT' , -
            object_name => 'FTS');
       PL/SQL procedure successfully completed

3. Start SQL Apply:

       SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
       Database altered

Troubleshooting ITL Pressure

Interested transaction list (ITL) pressure is reported in the alert log of the SQL Apply instance. Example A-3 shows an example of the warning messages.

Example A-3 Warning Messages Reported for ITL Pressure

    Tue Apr 22 15:50:42 2003
    WARNING: the following transaction makes no progress
    WARNING: in the last 30 seconds for the given message!
    WARNING: xid =
    0x0006.005.000029fa cscn = 2152982, message# = 2, slavid = 17

Real-Time Analysis

The messages shown in Example A-3 indicate that the SQL Apply process (`slavid`) #17 has not made any progress in the last 30 seconds. To determine the SQL statement being issued by the Apply process, issue the following query:

    SQL> SELECT SA.SQL_TEXT
      2    FROM V$SQLAREA SA
      3       , V$SESSION S
      4       , V$STREAMS_APPLY_SERVER SAS
      5   WHERE SAS.SERVER_ID = &SLAVEID
      6     AND S.SID = SAS.SID
      7   AND SA.ADDRESS = S.SQL_ADDRESS
    SQL_TEXT
    ------------------------------------------------------------
    insert into "APP"."LOAD_TAB_1" p("PK","TEXT")values(:1,:2)

An alternative method to identifying ITL pressure is to query the `V$LOCK` view, as shown in the following example. Any session that has a request value of 4 on a `TX` lock, is waiting for an ITL to become available.

    SQL> SELECT SID,TYPE,ID1,ID2,LMODE,REQUEST
      2    FROM V$LOCK
      3   WHERE TYPE = 'TX'

    SID        TY ID1        ID2        LMODE      REQUEST
    ---------- -- ---------- ---------- ---------- ----------
             8 TX     327688         48          6          0
            10 TX     327688         48          0          4

In this example, `SID 10` is waiting for the `TX` lock held by `SID 8`.

Post-Incident Review

Pressure for a segment's ITL is unlikely to last for an extended period of time. In addition, ITL pressure that lasts for less than 30 seconds will not be reported in the standby databases alert log. Therefore, to determine which objects have been subjected to ITL pressure, issue the following statement:

    SQL> SELECT SEGMENT_OWNER, SEGMENT_NAME, SEGMENT_TYPE
      2    FROM V$SEGMENT_STATISTICS
      3   WHERE STATISTIC_NAME = 'ITL WAITS'
      4     AND VALUE > 0
      5   ORDER BY VALUE

This statement reports all database segments that have had ITL pressure at some time since the instance was last started.

Note: This SQL statement is not limited to a logical standby databases in the Data Guard environment. It is applicable to any Oracle database.

Resolving ITL Pressure

To increase the `INITRANS` integer for a particular database object, it is necessary to first stop SQL Apply.

See Also: Oracle Database SQL Reference for more information about specifying the `INITRANS` integer, which it the initial number of concurrent transaction entries allocated within each data block allocated to the database object

The following example shows the necessary steps to increase the `INITRANS` for table `load_tab_1` in the schema `app`.

1. Stop SQL Apply:

       SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
       Database altered.

2. Temporarily bypass the database guard:

       SQL> ALTER SESSION DISABLE GUARD;
       Session altered.

3. Increase the `INITRANS` on the standby database. For example:

       SQL> ALTER TABLE APP.LOAD_TAB_1 INITRANS 30;
       Table altered

4. Reenable the database guard:

       SQL> ALTER SESSION ENABLE GUARD;
       Session altered

5. Start SQL Apply:

       SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
       Database altered.

Also, consider modifying the database object on the primary database, so in the event of a switchover, the error should not occur on the new standby database.

### A.9.4 Troubleshooting ORA-1403 Errors with Flashback Transactions

If SQL Apply returns the `ORA-1403: No Data Found` error, then it may be possible to use Flashback Transaction to reconstruct the missing data. This is reliant upon the `UNDO_RETENTION` initialization parameter specified on the standby database instance.

Under normal circumstances, the `ORA-1403` error should not be seen in a logical standby database environment. The error occurs when data in a table that is being managed by SQL Apply is modified directly on the standby database and then the same data is modified on the primary database. When the modified data is updated on the primary database and is subsequently received on the logical standby database, SQL Apply verifies the original version of the data is present on the standby database before updating the record. When this verification fails, the `ORA-1403: No Data Found` error is returned.

The Initial Error

When SQL Apply verification fails, the error message is reported in the alert log of the logical standby database and a record is inserted in the `DBA_LOGSTDBY_EVENTS` view.The information in the alert log is truncated, while the error is reported in it's entirety in the database view. For example:

    LOGSTDBY stmt: UPDATE "SCOTT"."MASTER"
      SET
        "NAME" = 'john'
      WHERE
        "PK" = 1 and
        "NAME" = 'andrew' and
        ROWID = 'AAAAAAAAEAAAAAPAAA'
    LOGSTDBY status: ORA-01403: no data found
    LOGSTDBY PID 1006, oracle@staco03 (P004)
    LOGSTDBY XID 0x0006.00e.00000417, Thread 1, RBA 0x02dd.00002221.10

The Investigation

The first step is to analyze the historical data of the table that caused the error. This can be achieved using the `VERSIONS` clause of the `SELECT` statement. For example, you can issue the following query on the primary database:

    SELECT VERSIONS_XID
          , VERSIONS_STARTSCN
          , VERSIONS_ENDSCN
          , VERSIONS_OPERATION
          , PK
          , NAME
       FROM SCOTT.MASTER
            VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
      WHERE PK = 1
      ORDER BY NVL(VERSIONS_STARTSCN,0);

    VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_ENDSCN V  PK NAME
    ---------------- ----------------- --------------- - --- -------
    03001900EE070000           3492279         3492290 I   1 andrew
    02000D00E4070000           3492290                 D   1 andrew

Depending upon the amount of undo retention that the database is configured to retain (`UNDO_RETENTION`) and the activity on the table, the information returned might be extensive and you may need to change the versions between syntax to restrict the amount of information returned.From the information returned, it can be seen that the record was first inserted at SCN 3492279 and then was deleted at SCN 3492290 as part of transaction ID 02000D00E4070000.Using the transaction ID, the database should be queried to find the scope of the transaction. This is achieved by querying the `FLASHBACK_TRANSACTION_QUERY` view.

    SELECT OPERATION     , UNDO_SQL  FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = HEXTORAW('02000D00E4070000');OPERATION  UNDO_SQL---------- ------------------------------------------------DELETE     insert into "SCOTT"."MASTER"("PK","NAME") values           ('1','andrew');BEGIN

Note that there is always one row returned representing the start of the transaction. In this transaction, only one row was deleted in the master table. The `UNDO_SQL` column when executed will restore the original data into the table.

    SQL> INSERT INTO "SCOTT"."MASTER"("PK","NAME") VALUES ('1','ANDREW');SQL> COMMIT;

When you restart SQL Apply, the transaction will be applied to the standby database:

    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;